import requests
import time
import random
from fake_useragent import UserAgent
import pymysql
from datetime import datetime
import schedule
import threading

# 随机User-Agent
ua = UserAgent()

# 数据库连接配置
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "litao021218",
    "database": "stock_data"
}


# 修改后的建表函数，添加时间戳字段
def create_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    create_table_sql = """
    CREATE TABLE IF NOT EXISTS `sina_kcb_stocks` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `symbol` VARCHAR(10) NOT NULL COMMENT '股票代码',
        `name` VARCHAR(50) NOT NULL COMMENT '股票名称',
        `trade` DECIMAL(10, 2) COMMENT '最新价',
        `pricechange` DECIMAL(10, 2) COMMENT '涨跌额',
        `changepercent` VARCHAR(10) COMMENT '涨跌幅',
        `buy` DECIMAL(10, 2) COMMENT '买一价',
        `sell` DECIMAL(10, 2) COMMENT '卖一价',
        `settlement` DECIMAL(10, 2) COMMENT '昨收',
        `open` DECIMAL(10, 2) COMMENT '今开',
        `high` DECIMAL(10, 2) COMMENT '最高',
        `low` DECIMAL(10, 2) COMMENT '最低',
        `volume` BIGINT COMMENT '成交量（手）',
        `amount` BIGINT COMMENT '成交额（元）',
        `update_time` DATETIME NOT NULL COMMENT '数据更新时间',
        `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
        UNIQUE KEY `idx_symbol_update_time` (`symbol`, `update_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='申万农业股票数据'
    """

    cursor.execute(create_table_sql)
    conn.commit()
    conn.close()
    print("表创建成功")


# 爬取数据函数（保持不变）
def crawl_sina_stock(page_start=1, page_end=3):
    url = "https://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getHQNodeData"
    all_stocks = []

    for page in range(page_start, page_end + 1):
        params = {
            "page": page,
            "num": 40,
            "sort": "symbol",
            "asc": 1,
            "node": "sw_nlmy",
            "symbol": "",
            "_s_r_a": "init"
        }
        headers = {
            "User-Agent": ua.random,
            "Referer": "https://vip.stock.finance.sina.com.cn/"
        }

        try:
            response = requests.get(url, params=params, headers=headers)
            if response.status_code == 200:
                data = response.json()
                all_stocks.extend(data)
                print(f"已爬取第 {page} 页，共 {len(data)} 条数据")
            else:
                print(f"第 {page} 页请求失败: HTTP {response.status_code}")
        except Exception as e:
            print(f"第 {page} 页出错: {e}")

        time.sleep(random.uniform(1, 3))  # 随机延迟1~3秒

    return all_stocks


# 修改后的存储函数，添加update_time字段
def save_to_mysql(stocks):
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 获取当前时间作为数据更新时间
    update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    for stock in stocks:
        try:
            cursor.execute("""
                INSERT INTO `sina_kcb_stocks` (
                    `symbol`, `name`, `trade`, `pricechange`, `changepercent`,
                    `buy`, `sell`, `settlement`, `open`, `high`, `low`,
                    `volume`, `amount`, `update_time`
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    `trade` = VALUES(`trade`),
                    `pricechange` = VALUES(`pricechange`),
                    `changepercent` = VALUES(`changepercent`),
                    `buy` = VALUES(`buy`),
                    `sell` = VALUES(`sell`),
                    `settlement` = VALUES(`settlement`),
                    `open` = VALUES(`open`),
                    `high` = VALUES(`high`),
                    `low` = VALUES(`low`),
                    `volume` = VALUES(`volume`),
                    `amount` = VALUES(`amount`)
            """, (
                stock.get("symbol"),
                stock.get("name"),
                float(stock.get("trade", 0)),
                float(stock.get("pricechange", 0)),
                stock.get("changepercent", "0%"),
                float(stock.get("buy", 0)),
                float(stock.get("sell", 0)),
                float(stock.get("settlement", 0)),
                float(stock.get("open", 0)),
                float(stock.get("high", 0)),
                float(stock.get("low", 0)),
                int(stock.get("volume", 0)),
                int(stock.get("amount", 0)),
                update_time
            ))
        except Exception as e:
            print(f"插入数据失败: {e}")

    conn.commit()
    conn.close()
    print(f"{update_time} 数据存储到MySQL完成！")


# 定时任务函数
def scheduled_task():
    print("开始执行定时任务...")
    try:
        stocks = crawl_sina_stock(page_start=1, page_end=3)
        save_to_mysql(stocks)
    except Exception as e:
        print(f"定时任务执行出错: {e}")


# 启动定时任务
def run_schedule():
    # 每5分钟执行一次
    schedule.every(3).minutes.do(scheduled_task)

    while True:
        schedule.run_pending()
        time.sleep(1)


# 主函数
if __name__ == "__main__":
    # 1. 建表
    create_table()

    # 2. 立即执行一次
    scheduled_task()

    # 3. 启动定时任务
    print("启动定时任务，每3分钟执行一次...")

    # 使用线程运行定时任务，避免阻塞
    schedule_thread = threading.Thread(target=run_schedule)
    schedule_thread.daemon = True
    schedule_thread.start()

    # 主线程可以继续做其他事情或保持运行
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        print("程序退出")